-- @owner: opentestcase033
-- @date: 2023-06-07
-- @testpoint: 只给源表读权限merge，权限不足，合理报错

--step1:创建测试用户测试表，只给源表读权限;expect:成功
drop user if exists u_privilege_0039 cascade;
create user u_privilege_0039 with password "test@123";
drop table if exists t_privilege_0039_1, t_privilege_0039_2 cascade;
create table t_privilege_0039_1(id int, body text, title text, last_mod_date date);
insert into t_privilege_0039_1 values(1, 'China, officially the People''s Republic of China(PRC), located in Asia, is the world''s most populous state.', 'China', '2010-1-1'),
(2, 'America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America', '2010-1-1'),
(3, 'England is a country that is part of the United Kingdom. It shares land borders with Scotland to the north and Wales to the west.', 'England','2010-1-1');
grant select on t_privilege_0039_1 to u_privilege_0039;
create table t_privilege_0039_2(id int, body text, title text, last_mod_date date);
grant select on t_privilege_0039_2 to u_privilege_0039;

--step2:创建打印函数;expect:成功
set role u_privilege_0039 password "test@123";
create or replace function f_privilege_0039(id integer, name varchar(100)) returns text language plpgsql immutable as $$
begin
raise notice 'id :% , name : %', id, name;
return 'aaa';
end;
$$;
/

--step3:执行merge命令;expect:权限不足时merge操作失败
merge into t_privilege_0039_2 np
using t_privilege_0039_1 p
on (np.id = p.id )
when matched then
  update set np.body = p.body, np.title = f_privilege_0039(p.id, p.title), np.last_mod_date = p.last_mod_date
when not matched then
  insert values (p.id, p.body, p.title, p.last_mod_date);

--step4:清理环境;expect:成功
reset role;
drop table t_privilege_0039_1;
drop table t_privilege_0039_2;
drop user u_privilege_0039 cascade;